package org.exam.util;

import org.exam.entry.Paper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


//jdbc连接工具类
public class JDBCUtil {
    private static PreparedStatement sta=null;
    private static Connection con=null;
    private static ResultSet rs=null;

    //静态代码块，获取连接对象，因为每一个连接对象都是相同的，所以加载一次就可以
    static {
        try {
            // DriverManager 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取连接
            // Connection 数据库连接对象  url（指定连接的路径 语法：“jdbc:mysql://ip地址:端口号/数据库名称”）
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC", "root", "12345678");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    //获取连接对象方法
    public static Connection getCon(){
        return  con;
    }

    //关闭方法
    public static void close(){
        try {
            if (rs!=null){
                rs.close();
            }
            if (sta!=null){
                sta.close();
            }
            if (con!=null){
                con.close();
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    // 执行查询操作
    public static List<Paper> select(Connection connection) throws SQLException {
        String query = "SELECT id, title,author, email FROM paper";
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(query)) {
            ArrayList<Paper> papers = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String title = resultSet.getString("title");
                String author = resultSet.getString("author");
                String email = resultSet.getString("email");
                papers.add(new Paper((long) id,title,author,null));
                System.out.println("ID: " + id + ",title: " + title + ", author: "+author);
            }
            return papers;
        }
    }

    /**
     * 插入论文方法
     * @param connection 连接对象
     * @param title 需要插入的论文标题
     * @param author 插入的论文作者
     * @throws SQLException
     */
    public static void insert(Connection connection, String title, String author) throws SQLException {
        String insertQuery = "INSERT INTO paper (title, author) VALUES (?, ?)";
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertQuery)) {
            preparedStatement.setString(1, title);
            preparedStatement.setString(2, author);

            int rowsAffected = preparedStatement.executeUpdate();
        }
    }

    /**
     * 更新操作
     * @param connection 连接对象
     * @param id 需要更新的论文id
     * @param title 更改后的论文标题
     * @throws SQLException
     */
    public static void update(Connection connection, int id, String title) throws SQLException {
        String updateQuery = "UPDATE paper SET title = ? WHERE id = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(updateQuery)) {
            preparedStatement.setString(1, title);
            preparedStatement.setInt(2, id);

            int rowsAffected = preparedStatement.executeUpdate();
        }
    }

    /**
     * 删除操作
     * @param connection 连接对象
     * @param id 需要删除的论文id
     * @throws SQLException
     */
    public static void delete(Connection connection, int id) throws SQLException {
        String deleteQuery = "DELETE FROM paper WHERE id = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(deleteQuery)) {
            preparedStatement.setInt(1, id);

            int rowsAffected = preparedStatement.executeUpdate();
        }
    }
}
